﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using uMES.LeanManufacturing.DBUtility;
using uMES.LeanManufacturing.ParameterDTO;

namespace uMES.LeanManufacturing.ReportBusiness
{
    public class uMESSysIntegrationBusiness
    {
        #region 添加设备状态记录
        public Boolean AddMDCResourceStatus(DataTable dtResourceStatus)
        {
            ArrayList SQLStringList = new ArrayList();

            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine("DELETE FROM mdcresourcestatus");

            SQLStringList.Add(strSQL.ToString());

            foreach (DataRow row in dtResourceStatus.Rows)
            {
                string strID = Guid.NewGuid().ToString();
                strSQL = new StringBuilder();
                strSQL.AppendLine("INSERT INTO mdcresourcestatus(ID,resourceno,factoryname,teamname,resourcestatus,collectdate)");
                strSQL.AppendLine("VALUES (");
                strSQL.AppendLine(string.Format("'{0}',", strID));
                strSQL.AppendLine(string.Format("'{0}',", row["ResourceNo"].ToString()));
                strSQL.AppendLine(string.Format("'{0}',", row["FactoryName"]));
                strSQL.AppendLine(string.Format("'{0}',", row["TeamName"]));
                strSQL.AppendLine(string.Format("'{0}',", row["ResourceStatus"]));
                strSQL.AppendLine(string.Format("TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS')", row["CollectDate"]));
                strSQL.AppendLine(")");

                SQLStringList.Add(strSQL.ToString());
            }

            OracleHelper.ExecuteSqlTran(SQLStringList);

            return true;
        }
        #endregion

        #region 查询MDC设备状态
        #region 分页查询
        public uMESPagingDataDTO GetSourceData(Dictionary<string, string> para, int intPageIndex, int intPageSize)
        {
            string strSQL = GetSQL_D(para);

            uMESPagingDataDTO retR = OracleHelper.GetPagingDataIns(strSQL, intPageIndex, intPageSize);
            return retR;
        }
        #endregion

        #region 不分页查询，用于导出
        public DataTable GetAllDataForOutExcel(Dictionary<string, string> para)
        {
            string strSQL = GetSQL_D(para);

            DataTable DT = OracleHelper.GetDataTable(strSQL);
            return DT;
        }
        #endregion

        #region 组合查询语句
        protected string GetSQL_D(Dictionary<string, string> para)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT rs.* FROM mdcresourcestatus rs");
            strQuery.AppendLine("WHERE 1 = 1");
            
            if (para.Keys.Contains("ResourceNo"))
            {
                if (!string.IsNullOrEmpty(para["ResourceNo"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(rs.resourceno) LIKE '%{0}%'", para["ResourceNo"].ToLower()));
                }
            }
            if (para.Keys.Contains("FactoryName"))
            {
                if (!string.IsNullOrEmpty(para["FactoryName"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(rs.factoryname) LIKE '%{0}%'", para["FactoryName"].ToLower()));
                }
            }
            if (para.Keys.Contains("TeamName"))
            {
                if (!string.IsNullOrEmpty(para["TeamName"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(rs.teamname) LIKE '%{0}%'", para["TeamName"].ToLower()));
                }
            }
            if (para.Keys.Contains("StartDate")) //开始时间
            {
                if (!string.IsNullOrEmpty(para["StartDate"]))
                {
                    strQuery.AppendLine(string.Format("AND rs.collectdate >= TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS')", para["StartDate"]));
                }
            }
            if (para.Keys.Contains("EndDate")) //结束时间
            {
                if (!string.IsNullOrEmpty(para["EndDate"]))
                {
                    strQuery.AppendLine(string.Format("AND rs.collectdate <= TO_DATE('{0} 23:59:59','yyyy-MM-dd HH24:MI:SS')", para["EndDate"]));
                }
            }

            strQuery.AppendLine("ORDER BY rs.collectdate DESC");

            return strQuery.ToString();
        }
        #endregion
        #endregion

        #region 添加DNC历史记录
        public Boolean AddToDNCHistory(Dictionary<string, string> para)
        {
            ArrayList SQLStringList = new ArrayList();

            //主表
            string strID = Guid.NewGuid().ToString();
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine("INSERT INTO todnchistory(ID,dispatchinfoid,oprtype,opremployeeid,oprdate,oprresult,notes)");
            strSQL.AppendLine("VALUES (");
            strSQL.AppendLine(string.Format("'{0}',", strID));
            strSQL.AppendLine(string.Format("'{0}',", para["DispatchInfoID"]));
            strSQL.AppendLine(string.Format("{0},", para["OprType"]));
            strSQL.AppendLine(string.Format("'{0}',", para["OprEmployeeID"]));
            strSQL.AppendLine(string.Format("TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS'),", para["OprDate"]));
            strSQL.AppendLine(string.Format("{0},", para["OprResult"]));
            strSQL.AppendLine(string.Format("'{0}'", para["Notes"]));
            strSQL.AppendLine(")");

            SQLStringList.Add(strSQL.ToString());

            OracleHelper.ExecuteSqlTran(SQLStringList);

            return true;
        }
        #endregion
    }
}
